﻿using System.Linq;
using System.Text;
using System.Diagnostics;
using System.Runtime.Serialization;
using System.Collections;
using System.Data;
using System.ComponentModel;
using System.Data.OracleClient;
namespace INTEX.DataBase.Tools
{
    public partial class INTEXDBC
    {
        public void InsertICC_VKAV95(DataRow dwData, int option)
        {
            or1 = OracleDB.CreateCommand();
            DataSet result = new DataSet();
            switch (option)
            {
                case 1:
                    or1.CommandText = "INSERT INTO ICC_VKAV95(VKPMANDANT, VKPAUF,"
                        + "VKPAUFKRS, VKPAUFLFD, VKPAUFPOS, PRODFACT,VKPKOLL, VKPFART,VKPART, VKPFARBE,  VKPMENGE1, VKPQTY,"
                        + "VKPMEEH,  VKPLIEFTERM, VKPZUGET, VKPFREIGE, VKPKOMMISS, VKPFAKT, VKPVERS, VKKSACHBE,VRQTY,VKPERL) "
                        + " SELECT VKPMANDANT,VKPAUFKRS||'-'||VKPAUFLFD,VKPAUFKRS,VKPAUFLFD,VKPAUFPOS,"
                        + " VKPKKOLL AS PRODFACT,VKPKOLL,RTRIM(VKPART)||'-'||RTRIM(VKPFARBE) as VKPFART,"
                        + " VKPART,VKPFARBE,VKPMENGE1*0.01, VKPMENGE1*0.01*VKPMEEHFKT*0.000001 AS VKPQTY,VKPMEEH, VKPLIEFTERM,VKPZUGET*0.01*VKPMEEHFKT*0.000001"
                        + ",VKPFREIGE*0.01*VKPMEEHFKT*0.000001,VKPKOMMISS*0.01*VKPMEEHFKT*0.000001,"
                        + " VKPFAKT,VKPVERS*0.01*0.01*VKPMEEHFKT*0.000001,VKKSACHBE,VKPMENGE1*0.01*VKPMEEHFKT*0.01*0.01*0.01,VKPERL  FROM VKPOS P JOIN VKKOPF "
                        + " ON VKKAUFKRS=VKPAUFKRS AND VKKAUFLFD=VKPAUFLFD WHERE VKPSA='  ' AND"
                        + "  NOT EXISTS (SELECT 1 FROM ICC_VKAV95 X WHERE  X.VKPMANDANT=P.VKPMANDANT "
                        + "  AND X.VKPAUFKRS=P.VKPAUFKRS AND X.VKPAUFLFD=P.VKPAUFLFD AND X.VKPAUFPOS=P.VKPAUFPOS)";
                    break;
                //DECODE(VKPLIEFTERM,0,'0',TO_CHAR(to_date(VKPLIEFTERM),'YYYY-MM-DD'))
                default:
                    break;
            }
            Connect2Proxy(or1);
        }
        public DataSet QueryICC_VKAV95(DataRow dwData, int option = 1)
        {
            DataSet Return = new DataSet();
            DataTable tbLocal = null;
            DataSet Result = new DataSet();
            string table = "ICC_VKAV95";

            or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 1:
                    or1.CommandText = "SELECT * FROM ICC_VKAV95 WHERE  "
                        + "  VKPMANDANT=:VKPMANDANT AND VKPAUFKRS=:VKPAUFKRS "
                        + " ORDER BY VKPAUF,VKPAUFPOS";
                    or1.Parameters.Add("VKPMANDANT", OracleType.NVarChar).Value = dwData["VKPMANDANT"].ToString();
                    or1.Parameters.Add("VKPAUFKRS", OracleType.NVarChar).Value = dwData["VKPAUFKRS"].ToString();
                    break;
                case 2:
                    or1.CommandText = "SELECT * FROM ICC_VKAV95 WHERE  VKPMANDANT=:VKPMANDANT AND VKPAUFKRS=:VKPAUFKRS AND VKPAUFLFD=:VKPAUFLFD";
                    or1.Parameters.Add("VKPMANDANT", OracleType.NVarChar).Value = dwData["VKPMANDANT"].ToString();
                    or1.Parameters.Add("VKPAUFKRS", OracleType.NVarChar).Value = dwData["VKPAUFKRS"].ToString();
                    or1.Parameters.Add("VKPAUFLFD", OracleType.NVarChar).Value = dwData["VKPAUFLFD"].ToString();
                    break;
                case 3:
                    or1.CommandText = "SELECT * FROM ICC_VKAV95 WHERE  VKPMANDANT=:VKPMANDANT AND VKPAUFKRS=:VKPAUFKRS AND VKPAUFLFD=:VKPAUFLFD "
                    + " AND VKPAUFPOS=:VKPAUFPOS";
                    or1.Parameters.Add("VKPMANDANT", OracleType.NVarChar).Value = dwData["VKPMANDANT"].ToString();
                    or1.Parameters.Add("VKPAUFKRS", OracleType.NVarChar).Value = dwData["VKPAUFKRS"].ToString();
                    or1.Parameters.Add("VKPAUFLFD", OracleType.NVarChar).Value = dwData["VKPAUFLFD"].ToString();
                    or1.Parameters.Add("VKPAUFPOS", OracleType.NVarChar).Value = dwData["VKPAUFPOS"].ToString();
                    break;
            }
            Result = Connect2Proxy(or1);
            if (Result.Tables.Count > 0)
            {
                tbLocal = Result.Tables[0];
                tbLocal.TableName = "ICC_VKAV95";
            }
            Return.Merge(Result);

            return Return;
        }
        public void UpdateICC_VKAV95(DataRow dwData, int option = 1)
        {
            OracleCommand or1;
            DataSet result = new DataSet();
            or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 1:
                    #region====Command====
                    or1.CommandText = "UPDATE ICC_VKAV95 SET "
                        + "PRODFACT=:PRODFACT,"
                        + "VKPART=:VKPART,"
                        + "VKPFARBE=:VKPFARBE,"
                        + "STRUCTURE=:STRUCTURE,"
                        + "FWIDTH=:FWIDTH,"
                        + "VKPMENGE1=:VKPMENGE1,"
                        + "VKPMEEH=:VKPMEEH,"
                        + "VKPTOL=:VKPTOL,"
                        + "VKPLIEFTERM=:VKPLIEFTERM,"
                        + "VKPZUGET=:VKPZUGET,"
                        + "VKPFREIGE=:VKPFREIGE,"
                        + "VKPKOMMISS=:VKPKOMMISS,"
                        + "VKPFAKT=:VKPFAKT,"
                        + "VKPVERS=:VKPVERS,"
                        + "FENTRYDAT=:FENTRYDAT,"
                        + "FQTY1=:FQTY1,"
                        + "FQTY1OVER=:FQTY1OVER,"
                        + "VKPOPENDEL=:VKPOPENDEL,"
                        + "FQTY2=:FQTY2,"
                        + "FQTY2REASON=:FQTY2REASON,"
                        + "VKPFRDAT=:VKPFRDAT,"
                        + "FSTOCK=:FSTOCK,"
                        + "VKKSACHBE=:VKKSACHBE,"
                        + "VRQTY=:VRQTY,"
                        + "REMARK=:REMARK "
                    + " WHERE  "
                    + "  VKPMANDANT=:VKPMANDANT AND VKPAUFKRS=:VKPAUFKRS AND VKPAUFLFD=:VKPAUFLFD AND VKPAUFPOS=:VKPAUFPOS";
                    #endregion

                    #region====Parameters====
                    or1.Parameters.Add("VKPMANDANT", OracleType.NVarChar).Value = dwData["VKPMANDANT"].ToString();
                    or1.Parameters.Add("VKPAUFKRS", OracleType.NVarChar).Value = dwData["VKPAUFKRS"].ToString();
                    or1.Parameters.Add("VKPAUFLFD", OracleType.NVarChar).Value = dwData["VKPAUFLFD"].ToString();
                    or1.Parameters.Add("VKPAUFPOS", OracleType.NVarChar).Value = dwData["VKPAUFPOS"].ToString();
                    or1.Parameters.Add("PRODFACT", OracleType.NVarChar).Value = dwData["PRODFACT"].ToString();
                    or1.Parameters.Add("VKPART", OracleType.NVarChar).Value = dwData["VKPART"].ToString();
                    or1.Parameters.Add("VKPFARBE", OracleType.NVarChar).Value = dwData["VKPFARBE"].ToString();
                    or1.Parameters.Add("STRUCTURE", OracleType.NVarChar).Value = dwData["STRUCTURE"].ToString();
                    or1.Parameters.Add("FWIDTH", OracleType.NVarChar).Value = dwData["FWIDTH"].ToString();
                    or1.Parameters.Add("VKPMENGE1", OracleType.NVarChar).Value = dwData["VKPMENGE1"].ToString();
                    or1.Parameters.Add("VKPMEEH", OracleType.NVarChar).Value = dwData["VKPMEEH"].ToString();
                    or1.Parameters.Add("VKPTOL", OracleType.NVarChar).Value = dwData["VKPTOL"].ToString();
                    or1.Parameters.Add("VKPLIEFTERM", OracleType.NVarChar).Value = dwData["VKPLIEFTERM"].ToString();
                    or1.Parameters.Add("VKPZUGET", OracleType.NVarChar).Value = dwData["VKPZUGET"].ToString();
                    or1.Parameters.Add("VKPFREIGE", OracleType.NVarChar).Value = dwData["VKPFREIGE"].ToString();
                    or1.Parameters.Add("VKPKOMMISS", OracleType.NVarChar).Value = dwData["VKPKOMMISS"].ToString();
                    or1.Parameters.Add("VKPFAKT", OracleType.NVarChar).Value = dwData["VKPFAKT"].ToString();
                    or1.Parameters.Add("VKPVERS", OracleType.NVarChar).Value = dwData["VKPVERS"].ToString();
                    or1.Parameters.Add("FENTRYDAT", OracleType.NVarChar).Value = dwData["FENTRYDAT"].ToString();
                    or1.Parameters.Add("FQTY1", OracleType.NVarChar).Value = dwData["FQTY1"].ToString();
                    or1.Parameters.Add("FQTY1OVER", OracleType.NVarChar).Value = dwData["FQTY1OVER"].ToString();
                    or1.Parameters.Add("VKPOPENDEL", OracleType.NVarChar).Value = dwData["VKPOPENDEL"].ToString();
                    or1.Parameters.Add("FQTY2", OracleType.NVarChar).Value = dwData["FQTY2"].ToString();
                    or1.Parameters.Add("FQTY2REASON", OracleType.NVarChar).Value = dwData["FQTY2REASON"].ToString();
                    or1.Parameters.Add("VKPFRDAT", OracleType.NVarChar).Value = dwData["VKPFRDAT"].ToString();
                    or1.Parameters.Add("FSTOCK", OracleType.NVarChar).Value = dwData["FSTOCK"].ToString();
                    or1.Parameters.Add("VKKSACHBE", OracleType.NVarChar).Value = dwData["VKKSACHBE"].ToString();
                    or1.Parameters.Add("VRQTY", OracleType.NVarChar).Value = dwData["VRQTY"].ToString();
                    or1.Parameters.Add("REMARK", OracleType.NVarChar).Value = dwData["REMARK"].ToString();
                    #endregion
                    break;
                case 2:
                    or1.CommandText = "UPDATE ICC_VKAV95 V SET (VKPFRDAT,VKPFREIGE,VKPVERS)="
                        + "(SELECT VKPFRDAT,VKPFREIGE*0.01*VKPMEEHFKT*0.000001,VKPVERS*0.01*VKPMEEHFKT*0.000001 FROM VKPOS P"
                        + " WHERE V.VKPAUFKRS=P.VKPAUFKRS AND V.VKPAUFLFD=P.VKPAUFLFD AND V.VKPAUFPOS=P.VKPAUFPOS AND VKPSA<>'AJ')"
                                               + " WHERE EXISTS  "
                        + " (SELECT 1 FROM VKPOS P WHERE V.VKPAUFKRS=P.VKPAUFKRS AND V.VKPAUFLFD=P.VKPAUFLFD AND V.VKPAUFPOS=P.VKPAUFPOS AND VKPSA<>'AJ')"
                        + " AND V.VKPERL=' '";
                    break;
                case 3:
                    or1.CommandText = "UPDATE ICC_VKAV95 V SET "
                        + "(VKPDART,VKPDVAR,VKPDFARBE,VKPDFINISH)"
                        + "=(SELECT VAFPART,VAFPVAR,VAFFFARBE,VAFBESCHICH FROM VAFARBEN F "
                        + " WHERE VAFKOLL=VKPKOLL AND F.VAFART=VKPART AND RTRIM(VAFFARBE)=RTRIM(VKPFARBE))"
                        + " WHERE EXISTS "
                        + " (SELECT 1 FROM VAFARBEN F WHERE  VAFKOLL=VKPKOLL AND F.VAFART=VKPART AND RTRIM(VAFFARBE)=RTRIM(VKPFARBE))"
                        //+"  AND RTRIM(VKPDFINISH)=''"
                        + " AND V.VKPERL=' '";
                    break;
                case 4:
                    or1.CommandText = "  UPDATE ICC_VKAV95 V SET (FSTOCK,FENTRYDAT)"
                        + "=(SELECT SUM(WBBISTKLG)*0.001,MIN(WBBEINDAT) FROM WBBST W WHERE "
                        + "WBBWAKZ='F' AND WBBART=VKPDART AND WBBVAR=VKPDVAR AND W.WBBFARBE=V.VKPDFARBE AND W.WBBBESCHICH=V.VKPDFINISH"
                        + " GROUP BY WBBART,WBBVAR,WBBFARBE,WBBBESCHICH)"
                             + " WHERE EXISTS  "
                        + " (SELECT 1 FROM WBBST W WHERE WBBWAKZ='F' AND W.WBBZENSUR=1 AND WBBZENSURKZ='A'"
                        + " AND WBBART=VKPDART AND WBBVAR=VKPDVAR AND W.WBBFARBE=V.VKPDFARBE "
                        + "AND W.WBBBESCHICH=V.VKPDFINISH)"
                        + " AND V.VKPERL=' '";
                    break;
                case 5:
                    or1.CommandText = "UPDATE ICC_VKAV95 V SET (FQTY1,FQTY2)"
                        + "=(SELECT SUM(WBBISTKLG)*0.001,0 FROM WBBST W WHERE WBBLB  IN ('CP','DC') AND  WBBWAKZ='F' AND W.WBBZENSUR='1' "
                        + "AND WBBART=VKPDART AND WBBVAR=VKPDVAR AND W.WBBFARBE=V.VKPDFARBE AND W.WBBBESCHICH=V.VKPDFINISH)"
                        + " WHERE EXISTS  "
                        + " (SELECT 1 FROM WBBST W WHERE WBBLB  IN ('CP','DC') AND WBBWAKZ='F' AND W.WBBZENSUR='1' "
                        + " AND WBBART=VKPDART AND WBBVAR=VKPDVAR AND W.WBBFARBE=V.VKPDFARBE "
                        + "AND W.WBBBESCHICH=V.VKPDFINISH)"
                        + " AND V.VKPERL=' '";
                    break;
                case 6:
                    or1.CommandText = "UPDATE ICC_VKAV95 V SET (FQTY2)"
                        + "=(SELECT SUM(WBBISTKLG)*0.001 FROM WBBST W WHERE WBBLB  IN ('CP','DC') AND WBBWAKZ='F' AND  W.WBBZENSUR='2'  "
                        + "AND WBBART=VKPDART AND WBBVAR=VKPDVAR AND W.WBBFARBE=V.VKPDFARBE AND W.WBBBESCHICH=V.VKPDFINISH)"
                                                  + " WHERE EXISTS  "
                        + " (SELECT 1 FROM WBBST W WHERE WBBLB  IN ('CP','DC') AND WBBWAKZ='F' AND W.WBBZENSUR='2' "
                        + " AND WBBART=VKPDART AND WBBVAR=VKPDVAR AND W.WBBFARBE=V.VKPDFARBE "
                        + "AND W.WBBBESCHICH=V.VKPDFINISH)"
                        + " AND V.VKPERL=' '";
                    break;
                case 7:
                    or1.CommandText = "UPDATE ICC_VKAV95 V SET FQTY1OVER=FQTY1-VKPQTY,VKPOPENDEL=FQTY1-VKPFREIGE"
                        + " WHERE V.VKPERL=' '";
                    break;
                case 8:
                    or1.CommandText = "   UPDATE ICC_VKAV95 V "
                        + " SET (STRUCTURE)=(SELECT STRAGG(TRIM(T2.TXTKTEXT))OVER(PARTITION BY T1.TXTISIND)"
                        + " FROM TXTIND T1 JOIN TXTKON T2 ON T1.TXTIMANDANT = T2.TXTKMANDANT AND T1.TXTIPIND = T2.TXTKPIND"
                        + "  WHERE RTRIM(VKPKOLL||VKPART)=RTRIM(TXTISIND)"
                        + "  AND T1.txtitxtart = 'VAD')"
                        + " WHERE EXISTS"
                        + " (SELECT 1 FROM TXTIND T1 JOIN TXTKON T2 ON T1.TXTIMANDANT = T2.TXTKMANDANT AND T1.TXTIPIND = T2.TXTKPIND"
                        + "  WHERE RTRIM(VKPKOLL||VKPART)=RTRIM(TXTISIND)"
                        + "  AND T1.txtitxtart = 'VAD') "
                        //+ "  AND T1.txtitxtart = 'VAD') AND SUBSTR(STRUCTURE,2 )='  '"
                        + " AND V.VKPERL=' '";
                    /*or1.CommandText = "   UPDATE ICC_VKAV95 V "
                        + " SET (STRUCTURE)=(SELECT RTRIM(T2.TXTKTEXT)"
                        + " FROM TXTIND T1 JOIN TXTKON T2 ON T1.TXTIMANDANT = T2.TXTKMANDANT AND T1.TXTIPIND = T2.TXTKPIND"
                        + "  WHERE RTRIM(VKPKOLL||VKPART)=RTRIM(TXTISIND)"
                        + "  AND T1.txtitxtart = 'VAD')"
                        + " WHERE EXISTS"
                        + " (SELECT 1 FROM TXTIND T1 JOIN TXTKON T2 ON T1.TXTIMANDANT = T2.TXTKMANDANT AND T1.TXTIPIND = T2.TXTKPIND"
                        + "  WHERE RTRIM(VKPKOLL||VKPART)=RTRIM(TXTISIND)"
                        + "  AND T1.txtitxtart = 'VAD') "
                        //+ "  AND T1.txtitxtart = 'VAD') AND RTRIM(STRUCTURE)=''"
                        + " AND V.VKPERL=' '";*/
                    break;
                case 9:
                    or1.CommandText = "UPDATE ICC_VKAV95 V SET (V.FWIDTH)="
                        + "(SELECT(VAABRTBIS*0.01-1)||'-'||VAABRTBIS*0.01||' '||VAAMEBRT FROM VAART P"
                        + " WHERE V.VKPKOLL=P.VAAKOLL AND V.VKPART=P.VAAART)"
                                               + " WHERE EXISTS  "
                        + " (SELECT 1 FROM VAART P WHERE V.VKPKOLL=P.VAAKOLL AND V.VKPART=P.VAAART)"
                        + " AND VKPERL=' '";
                    break;
                case 9999:
                    or1.CommandText = "UPDATE ICC_VKAV95 V SET (V.VKPERL)="
                        + "(SELECT P.VKPERL FROM VKPOS P"
                        + " WHERE V.VKPAUFKRS=P.VKPAUFKRS AND V.VKPAUFLFD=P.VKPAUFLFD AND V.VKPAUFPOS=P.VKPAUFPOS AND VKPSA<>'AJ')"
                                               + " WHERE EXISTS  "
                        + " (SELECT 1 FROM VKPOS P WHERE V.VKPAUFKRS=P.VKPAUFKRS AND V.VKPAUFLFD=P.VKPAUFLFD AND V.VKPAUFPOS=P.VKPAUFPOS AND VKPSA<>'AJ')"
                        + " AND VKPERL=' '";
                    break;
                default:
                    break;
            }
            Connect2Proxy(or1);
        }
    }
}
